.ipynb .pdf

repository open issue

Binder Colab

Contents

3. Exploratory Data Analysis

Exploratory data analysis (EDA) involves taking a first look at a dataset and summarising its salient characteristics using tables and graphics. It is (or should be) the stage before testing hypotheses and can be useful in informing hypotheses. In this chapter, we’ll look at a few options for EDA using code.

To show how to do EDA using code, we will need a dataset to explore. We’ll use the Grinell housing dataset, which covers houses sold between 2005 and 2015 in Grinnell, Iowa. It was assembled by a local estate agent, Matt Karjalahti.

First, let’s import a couple of essential packages:

import pandas as pd
import matplotlib.pyplot as plt
import numpy as np
from itertools import islice

# Plot settings
plt.style.use(
    "https://github.com/aeturrell/coding-for-economists/raw/main/plot_style.txt"
)

3.1. Preliminaries: extracting, typing, and initial cleaning

Let’s grab some data to do exploratory data analysis on.

df = pd.read_csv(
    "https://vincentarelbundock.github.io/Rdatasets/csv/Stat2Data/GrinnellHouses.csv",
    index_col=0,
)
df.head()

	Date	Address	Bedrooms	Baths	SquareFeet	LotSize	YearBuilt	YearSold	MonthSold	DaySold	CostPerSqFt	OrigPrice	ListPrice	SalePrice	SPLPPct
1	16695	1510 First Ave #112	2	1.0	1120.0	NaN	1993	2005	9	16	6.25	17000	10500	7000	66.67
2	16880	1020 Center St	3	1.0	1224.0	0.172176	1900	2006	3	20	22.06	35000	35000	27000	77.14
3	16875	918 Chatterton St	4	1.0	1540.0	NaN	1970	2006	3	15	18.18	54000	47000	28000	59.57
4	16833	1023 & 1025 Spring St.	3	1.0	1154.0	NaN	1900	2006	2	1	26.00	65000	49000	30000	61.22
5	16667	503 2nd Ave	3	1.0	1277.0	0.206612	1900	2005	8	19	24.08	35000	35000	30750	87.86

There are some things we should do even before an exploratory data analysis; we should make sure we’ve cast the columns to the right types and converted any that need converting. Even a quick look at the data provided by .head() shows that the Date column is in a weird format. It also seems like the number of Baths column has some data issues because it is a floating point number rather than an integer. However, this a North American quirk: realtors refer to bathrooms with only a toilet and sink as a “half bath” (and sometimes those with a shower but no bathtub as a “three-quarter bath,” which also shows up in the data). So this is okay, and we mainly need concern ourselves with the Date column.

Fortunately, there is guidance attached to the dataset as to what the Date column really means: it’s just a counter of days that encodes the date of sale that begins with 1st Jan 2005=16436. The documents also tell us that the final year is 2015. We’d really like to have a more usable datetime column than this so let’s sort this out first. Ideally, we’d like to map the Date column into a new datetime column that turns 16436 into 1st Jan 2005, 16437 into 2nd Jan 2005, and so on, until we have a mapping for every date between the first date in the dataset and the last.

Our solution will be to create a function that maps the code for Date into a genuine datetime using the pd.DateOffset function and apply it to the Date column using apply:

def convert_date_code_to_datetime(date):
    start_code = 16436
    return pd.to_datetime("01-01-2005") + pd.DateOffset(days=date - start_code)


df["datetime"] = df["Date"].apply(convert_date_code_to_datetime)
# Check the 'biggest' rows by datetime
df.nlargest(5, columns=["datetime"])

	Date	Address	Bedrooms	Baths	SquareFeet	LotSize	YearBuilt	YearSold	MonthSold	DaySold	CostPerSqFt	OrigPrice	ListPrice	SalePrice	SPLPPct	datetime
915	20186	957 350th Ave	3	2.00	1700.0	11.430000	1966	2015	4	8	128.82	229000	229000	219000	95.63	2015-04-09
886	20185	232 4th Ave W	3	2.50	NaN	0.139991	2004	2015	4	7	0.00	137500	137500	128500	93.45	2015-04-08
905	20185	1726 Summer St	4	1.75	1512.0	0.530000	1996	2015	4	7	125.00	239900	219900	189000	85.95	2015-04-08
920	20184	108 East St	4	3.00	1650.0	0.320000	2013	2015	4	6	156.97	259000	259000	259000	100.00	2015-04-07
834	20181	1405 Prince St	3	1.50	1094.0	NaN	1900	2015	4	3	18.28	32500	32500	20000	61.54	2015-04-04

An extra column containing datetime has been added and, looking at the max rows, we can see that it does indeed run all the way to 2015 as expected from the documentation of the dataset.

Okay, now we want to sort out the data type isses we saw earlier. But let’s just check they’re as bad as we think using df.info()

df.info()

<class 'pandas.core.frame.DataFrame'>
Int64Index: 929 entries, 1 to 929
Data columns (total 16 columns):
 #   Column       Non-Null Count  Dtype         
---  ------       --------------  -----         
 0   Date         929 non-null    int64         
 1   Address      929 non-null    object        
 2   Bedrooms     929 non-null    int64         
 3   Baths        929 non-null    float64       
 4   SquareFeet   911 non-null    float64       
 5   LotSize      741 non-null    float64       
 6   YearBuilt    929 non-null    int64         
 7   YearSold     929 non-null    int64         
 8   MonthSold    929 non-null    int64         
 9   DaySold      929 non-null    int64         
 10  CostPerSqFt  929 non-null    float64       
 11  OrigPrice    929 non-null    int64         
 12  ListPrice    929 non-null    int64         
 13  SalePrice    929 non-null    int64         
 14  SPLPPct      929 non-null    float64       
 15  datetime     929 non-null    datetime64[ns]
dtypes: datetime64[ns](1), float64(5), int64(9), object(1)
memory usage: 123.4+ KB

pandas read most of them in sensibly, but not all of them, so let’s adjust the ones we need to:

df = df.assign(
    Address=df["Address"].astype("string"),
    Bedrooms=df["Bedrooms"].astype("category"),
    OrigPrice=df["OrigPrice"].astype(float),
    ListPrice=df["ListPrice"].astype(float),
    SalePrice=df["SalePrice"].astype(float),
)
df.info()

<class 'pandas.core.frame.DataFrame'>
Int64Index: 929 entries, 1 to 929
Data columns (total 16 columns):
 #   Column       Non-Null Count  Dtype         
---  ------       --------------  -----         
 0   Date         929 non-null    int64         
 1   Address      929 non-null    string        
 2   Bedrooms     929 non-null    category      
 3   Baths        929 non-null    float64       
 4   SquareFeet   911 non-null    float64       
 5   LotSize      741 non-null    float64       
 6   YearBuilt    929 non-null    int64         
 7   YearSold     929 non-null    int64         
 8   MonthSold    929 non-null    int64         
 9   DaySold      929 non-null    int64         
 10  CostPerSqFt  929 non-null    float64       
 11  OrigPrice    929 non-null    float64       
 12  ListPrice    929 non-null    float64       
 13  SalePrice    929 non-null    float64       
 14  SPLPPct      929 non-null    float64       
 15  datetime     929 non-null    datetime64[ns]
dtypes: category(1), datetime64[ns](1), float64(8), int64(5), string(1)
memory usage: 117.4 KB

Having to do some initial variable type cleaning is a normal and unavoidable part of data analysis, especially when reading in from a format like CSV (which does not preserve data type but has great interoperability across systems). It’s important and what we’ve seen here is a typical pattern.

There’s just one more step before starting the EDA proper. If you haven’t reached the Chapter on best practice, which talks about naming conventions, you won’t know this yet but the usual naming convention for variables and columns in Python is so-called snake case (appropriately enough). An example of a word not in snake case would be ‘ThisIsAColName’; actually that’s in Camel Case. Rendered in snake case, it would be ‘this_is_a_col_name’, which is longer but slightly easier to read. So we should really change the column names to be in snake case. Fortunately, the dataprep package has us covered here; it provides methods to translate between pretty much every naming convention that exists. Let’s change our columns to snake case, which is the default setting of clean_headers.

TODO: add this example when dataprep updated

from dataprep.clean import clean_headers

df = clean_headers(df)

Now we can start the exploratory data analysis!

3.2. EDA using pandas built-in methods

pandas has some great options for built-in EDA; in fact we’ve already seen one of them, df.info() which, as well as reporting datatypes and memory usage, also tells us how many observations in each column are ‘truthy’ rather than ‘falsy’, ie how many have non-null values.

3.2.1. Exploratory tables and descriptive statistics

A small step beyond df.info to get tables is to use df.describe() which, if you have mixed datatypes that include floats, will report some basic summary statistics:

df.describe()

	Date	Baths	SquareFeet	LotSize	YearBuilt	YearSold	MonthSold	DaySold	CostPerSqFt	OrigPrice	ListPrice	SalePrice	SPLPPct
count	929.000000	929.000000	911.000000	741.000000	929.000000	929.000000	929.000000	929.000000	929.000000	929.000000	929.000000	929.000000	929.000000
mean	18223.215285	1.779333	1583.215148	0.723463	1945.903122	2009.365985	6.831001	16.226050	83.291561	146047.083961	141399.138859	133203.720129	93.563994
std	1139.171002	0.743154	655.664966	2.725697	37.209902	3.124149	3.035790	9.396815	35.762241	85174.109372	82228.201763	77959.624697	6.953764
min	16527.000000	0.000000	640.000000	0.028926	1870.000000	2005.000000	1.000000	1.000000	0.000000	5990.000000	10500.000000	7000.000000	46.500000
25%	17169.000000	1.000000	1150.000000	0.233884	1900.000000	2007.000000	5.000000	8.000000	60.820000	89900.000000	88000.000000	83000.000000	91.320000
50%	18046.000000	1.750000	1440.000000	0.284091	1956.000000	2009.000000	7.000000	16.000000	82.880000	129900.000000	125000.000000	119340.000000	94.920000
75%	19281.000000	2.000000	1833.000000	0.370179	1973.000000	2012.000000	9.000000	25.000000	105.770000	179000.000000	172000.000000	162500.000000	97.560000
max	20186.000000	6.000000	6815.000000	55.000000	2013.000000	2015.000000	12.000000	31.000000	258.790000	695000.000000	695000.000000	606000.000000	111.020000

Although helpful, that sure is hard to read! We can improve this by using the round() method too:

sum_table = df.describe().round(1)
sum_table

	Date	Baths	SquareFeet	LotSize	YearBuilt	YearSold	MonthSold	DaySold	CostPerSqFt	OrigPrice	ListPrice	SalePrice	SPLPPct
count	929.0	929.0	911.0	741.0	929.0	929.0	929.0	929.0	929.0	929.0	929.0	929.0	929.0
mean	18223.2	1.8	1583.2	0.7	1945.9	2009.4	6.8	16.2	83.3	146047.1	141399.1	133203.7	93.6
std	1139.2	0.7	655.7	2.7	37.2	3.1	3.0	9.4	35.8	85174.1	82228.2	77959.6	7.0
min	16527.0	0.0	640.0	0.0	1870.0	2005.0	1.0	1.0	0.0	5990.0	10500.0	7000.0	46.5
25%	17169.0	1.0	1150.0	0.2	1900.0	2007.0	5.0	8.0	60.8	89900.0	88000.0	83000.0	91.3
50%	18046.0	1.8	1440.0	0.3	1956.0	2009.0	7.0	16.0	82.9	129900.0	125000.0	119340.0	94.9
75%	19281.0	2.0	1833.0	0.4	1973.0	2012.0	9.0	25.0	105.8	179000.0	172000.0	162500.0	97.6
max	20186.0	6.0	6815.0	55.0	2013.0	2015.0	12.0	31.0	258.8	695000.0	695000.0	606000.0	111.0

Now, although this is very basic, let’s say you wanted to include it as a table of summary statistics in a paper. This is just a dataframe so you can export it just like you can any pandas dataframe. This includes options like .to_excel. For inclusion in a paper or report, you’re most likely to want to report it as text:

print(sum_table.to_string())

          Date  Baths  SquareFeet  LotSize  YearBuilt  YearSold  MonthSold  DaySold  CostPerSqFt  OrigPrice  ListPrice  SalePrice  SPLPPct
count    929.0  929.0       911.0    741.0      929.0     929.0      929.0    929.0        929.0      929.0      929.0      929.0    929.0
mean   18223.2    1.8      1583.2      0.7     1945.9    2009.4        6.8     16.2         83.3   146047.1   141399.1   133203.7     93.6
std     1139.2    0.7       655.7      2.7       37.2       3.1        3.0      9.4         35.8    85174.1    82228.2    77959.6      7.0
min    16527.0    0.0       640.0      0.0     1870.0    2005.0        1.0      1.0          0.0     5990.0    10500.0     7000.0     46.5
25%    17169.0    1.0      1150.0      0.2     1900.0    2007.0        5.0      8.0         60.8    89900.0    88000.0    83000.0     91.3
50%    18046.0    1.8      1440.0      0.3     1956.0    2009.0        7.0     16.0         82.9   129900.0   125000.0   119340.0     94.9
75%    19281.0    2.0      1833.0      0.4     1973.0    2012.0        9.0     25.0        105.8   179000.0   172000.0   162500.0     97.6
max    20186.0    6.0      6815.0     55.0     2013.0    2015.0       12.0     31.0        258.8   695000.0   695000.0   606000.0    111.0

as Markdown:

print(sum_table.to_markdown())

|       |    Date |   Baths |   SquareFeet |   LotSize |   YearBuilt |   YearSold |   MonthSold |   DaySold |   CostPerSqFt |   OrigPrice |   ListPrice |   SalePrice |   SPLPPct |
|:------|--------:|--------:|-------------:|----------:|------------:|-----------:|------------:|----------:|--------------:|------------:|------------:|------------:|----------:|
| count |   929   |   929   |        911   |     741   |       929   |      929   |       929   |     929   |         929   |       929   |       929   |       929   |     929   |
| mean  | 18223.2 |     1.8 |       1583.2 |       0.7 |      1945.9 |     2009.4 |         6.8 |      16.2 |          83.3 |    146047   |    141399   |    133204   |      93.6 |
| std   |  1139.2 |     0.7 |        655.7 |       2.7 |        37.2 |        3.1 |         3   |       9.4 |          35.8 |     85174.1 |     82228.2 |     77959.6 |       7   |
| min   | 16527   |     0   |        640   |       0   |      1870   |     2005   |         1   |       1   |           0   |      5990   |     10500   |      7000   |      46.5 |
| 25%   | 17169   |     1   |       1150   |       0.2 |      1900   |     2007   |         5   |       8   |          60.8 |     89900   |     88000   |     83000   |      91.3 |
| 50%   | 18046   |     1.8 |       1440   |       0.3 |      1956   |     2009   |         7   |      16   |          82.9 |    129900   |    125000   |    119340   |      94.9 |
| 75%   | 19281   |     2   |       1833   |       0.4 |      1973   |     2012   |         9   |      25   |         105.8 |    179000   |    172000   |    162500   |      97.6 |
| max   | 20186   |     6   |       6815   |      55   |      2013   |     2015   |        12   |      31   |         258.8 |    695000   |    695000   |    606000   |     111   |

as HTML:

print(sum_table.head(3).to_html())

<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>Date</th>
      <th>Baths</th>
      <th>SquareFeet</th>
      <th>LotSize</th>
      <th>YearBuilt</th>
      <th>YearSold</th>
      <th>MonthSold</th>
      <th>DaySold</th>
      <th>CostPerSqFt</th>
      <th>OrigPrice</th>
      <th>ListPrice</th>
      <th>SalePrice</th>
      <th>SPLPPct</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>count</th>
      <td>929.0</td>
      <td>929.0</td>
      <td>911.0</td>
      <td>741.0</td>
      <td>929.0</td>
      <td>929.0</td>
      <td>929.0</td>
      <td>929.0</td>
      <td>929.0</td>
      <td>929.0</td>
      <td>929.0</td>
      <td>929.0</td>
      <td>929.0</td>
    </tr>
    <tr>
      <th>mean</th>
      <td>18223.2</td>
      <td>1.8</td>
      <td>1583.2</td>
      <td>0.7</td>
      <td>1945.9</td>
      <td>2009.4</td>
      <td>6.8</td>
      <td>16.2</td>
      <td>83.3</td>
      <td>146047.1</td>
      <td>141399.1</td>
      <td>133203.7</td>
      <td>93.6</td>
    </tr>
    <tr>
      <th>std</th>
      <td>1139.2</td>
      <td>0.7</td>
      <td>655.7</td>
      <td>2.7</td>
      <td>37.2</td>
      <td>3.1</td>
      <td>3.0</td>
      <td>9.4</td>
      <td>35.8</td>
      <td>85174.1</td>
      <td>82228.2</td>
      <td>77959.6</td>
      <td>7.0</td>
    </tr>
  </tbody>
</table>

or as latex:

print(sum_table.to_latex(caption="Summary stats from EDA"))

\begin{table}
\centering
\caption{Summary stats from EDA}
\begin{tabular}{lrrrrrrrrrrrrr}
\toprule
{} &     Date &  Baths &  SquareFeet &  LotSize &  YearBuilt &  YearSold &  MonthSold &  DaySold &  CostPerSqFt &  OrigPrice &  ListPrice &  SalePrice &  SPLPPct \\
\midrule
count &    929.0 &  929.0 &       911.0 &    741.0 &      929.0 &     929.0 &      929.0 &    929.0 &        929.0 &      929.0 &      929.0 &      929.0 &    929.0 \\
mean  &  18223.2 &    1.8 &      1583.2 &      0.7 &     1945.9 &    2009.4 &        6.8 &     16.2 &         83.3 &   146047.1 &   141399.1 &   133203.7 &     93.6 \\
std   &   1139.2 &    0.7 &       655.7 &      2.7 &       37.2 &       3.1 &        3.0 &      9.4 &         35.8 &    85174.1 &    82228.2 &    77959.6 &      7.0 \\
min   &  16527.0 &    0.0 &       640.0 &      0.0 &     1870.0 &    2005.0 &        1.0 &      1.0 &          0.0 &     5990.0 &    10500.0 &     7000.0 &     46.5 \\
25\%   &  17169.0 &    1.0 &      1150.0 &      0.2 &     1900.0 &    2007.0 &        5.0 &      8.0 &         60.8 &    89900.0 &    88000.0 &    83000.0 &     91.3 \\
50\%   &  18046.0 &    1.8 &      1440.0 &      0.3 &     1956.0 &    2009.0 &        7.0 &     16.0 &         82.9 &   129900.0 &   125000.0 &   119340.0 &     94.9 \\
75\%   &  19281.0 &    2.0 &      1833.0 &      0.4 &     1973.0 &    2012.0 &        9.0 &     25.0 &        105.8 &   179000.0 &   172000.0 &   162500.0 &     97.6 \\
max   &  20186.0 &    6.0 &      6815.0 &     55.0 &     2013.0 &    2015.0 &       12.0 &     31.0 &        258.8 &   695000.0 &   695000.0 &   606000.0 &    111.0 \\
\bottomrule
\end{tabular}
\end{table}

And remember, with all of these, you can write them to file using the following structure:

open('sum_stats_table.tex', 'w').write(sum_table.to_latex(caption='Summary stats from EDA'))

Of course, the stats provided in this pre-built table are not very customised. So what do we do to get the table that we actually want? Well, the answer is to draw on the contents of the previous data chapters, particularly the introduction to data analysis. Groupbys, merges, aggregations: use all of them to produce the EDA table that you want.

If you’re exploring data, you might also want to be able to read everything clearly and see any deviations from what you’d expect quickly. pandas has some built-in functionality that styles dataframes to help you. These styles persist when you export the dataframe to, say, Excel, too.

Here’s an example that highlights some ways of styling dataframes, making use of several features such as: changing the units (lambda function), unstacking into a wider format (unstack), fill NaNs with unobtrusive strings (.fillna('-')), removing numbers after the decimal place (.style.set_precision(0)), and adding a caption (.style.set_caption).

(
    df.groupby(["YearSold", "Bedrooms"])
    .mean()["SalePrice"]
    .apply(lambda x: x / 1e3)
    .unstack()
    .fillna("-")
    .style.set_precision(0)
    .set_caption("Sale price (thousands)")
)

Sale price (thousands)

Bedrooms	0	1	2	3	4	5	6	7	8
YearSold
2005	155	-	82	112	141	174	205	81	-
2006	25	-	80	125	139	281	-	-	205
2007	-	-	83	110	171	245	400	-	-
2008	238	62	82	132	158	236	221	-	-
2009	-	84	78	104	129	159	-	-	-
2010	-	-	68	120	161	195	-	-	-
2011	128	-	83	128	176	213	-	-	-
2012	191	38	98	132	154	213	305	-	-
2013	382	88	77	129	197	182	141	-	-
2014	55	65	94	147	157	198	-	-	-
2015	-	-	93	126	213	-	-	-	-

Although a neater one than we’ve seen, this is still a drab table of numbers. The eye is not immediately drawn to it!

To remedy that, let’s take a look at another styling technique: the use of colour. Let’s say we wanted to make a table that showed a cross-tabulation between year and number of bathrooms. Naturally, we’ll use pd.crosstab but we’ll ask that the values that appear in the table (counts) be lit up with a heatmap:

pd.crosstab(df["Bedrooms"], df["YearSold"]).style.background_gradient(cmap="plasma")

YearSold	2005	2006	2007	2008	2009	2010	2011	2012	2013	2014	2015
Bedrooms
0	1	1	0	1	0	0	3	3	1	2	0
1	0	0	0	2	1	0	0	1	2	1	0
2	15	18	24	17	14	10	10	11	18	11	1
3	54	64	56	59	25	27	31	47	39	46	15
4	21	39	24	17	10	14	14	28	28	30	3
5	7	8	6	8	3	2	8	6	4	9	0
6	1	0	1	2	0	0	0	1	2	0	0
7	1	0	0	0	0	0	0	0	0	0	0
8	0	1	0	0	0	0	0	0	0	0	0

Here are a couple of other styling tips for dataframes.

First, use bars to show ordering:

(
    df.iloc[:10, -6:-1]
    .style.set_precision(0)
    .bar(subset=["CostPerSqFt", "SalePrice"], color="#d65f5f")
)

	CostPerSqFt	OrigPrice	ListPrice	SalePrice	SPLPPct
1	6	17000	10500	7000	67
2	22	35000	35000	27000	77
3	18	54000	47000	28000	60
4	26	65000	49000	30000	61
5	24	35000	35000	30750	88
6	39	45900	45900	42000	92
7	60	62500	56500	46000	81
8	43	54500	52500	49500	94
9	55	59900	52500	50000	95
10	34	50000	50000	50000	100

Second, use format to add informative suffixes and round numbers appropriately:

df.groupby(["Bedrooms"])[["SPLPPct"]].mean().style.format("{:.0f}%")

	SPLPPct
Bedrooms
0	97%
1	89%
2	92%
3	94%
4	94%
5	93%
6	90%
7	95%
8	82%

Third, use .hightlight_max, and similar commands, to show important entries:

df.iloc[:10, -6:-1].style.highlight_max().format("{:.0f}")

	CostPerSqFt	OrigPrice	ListPrice	SalePrice	SPLPPct
1	6	17000	10500	7000	67
2	22	35000	35000	27000	77
3	18	54000	47000	28000	60
4	26	65000	49000	30000	61
5	24	35000	35000	30750	88
6	39	45900	45900	42000	92
7	60	62500	56500	46000	81
8	43	54500	52500	49500	94
9	55	59900	52500	50000	95
10	34	50000	50000	50000	100

You can find a full set of styling commands here.

3.2.2. Exploratory plotting

pandas has some built-in plotting options to help you look at data quickly. These can be accessed via .plot.* or .plot(), depending on the context. Let’s make a quick .plot() of the mean quarterly nominal Sale Price over time (with extra options passed via keyword arguments):

(
    df.set_index("datetime")
    .groupby(pd.Grouper(freq="3M"))["SalePrice"]
    .mean()
    .apply(lambda x: x / 1e3)
    .plot(
        title="House sales in Grinnell, Iowa",
        xlabel="",
        ylabel="Mean nominal sale price (000s USD)",
    )
);

Again, if you can get the data in the right shape, you can plot it. The same function works with multiple lines

(
    df.set_index("datetime")
    .groupby(pd.Grouper(freq="3M"))[["OrigPrice", "ListPrice", "SalePrice"]]
    .mean()
    .apply(lambda x: x / 1e3)
    .plot(style=["-", ":", "-."])
);

Now let’s see some of the other quick .plot.* options.

Here’s a KDE plot. Just to show what’s possible, we’ll use the df.columnname syntax, an alternative to df['columnname'], and setting limits via keyword arguments.

df.SquareFeet.plot.kde(ylim=(0, None), xlim=(0, None));

A bar chart (use barh for horizontal orientation; rot sets rotation of labels):

df.value_counts("Bedrooms").sort_index().plot.bar(title="Counts", rot=0);

This chart shows why EDA is so important: if we are modelling residential housing, and we find that there are observations that don’t have any bedrooms, perhaps they should be excluded from the downstream analysis.

This next one, using .plot.hist, provides another instructive example of how EDA can reveal interesting issues with datasets. First, there are many more houses built in 1900 than seem plausible and, checking the documentation for the dataset, it does say that when a construction date is not known, it is entered as 1900-which explains what’s going on here. Second, some of the construction years are in the future! Is this plausible? It could be. Some houses are sold before the construction of an estate is completed; this could be the case here.

df["YearBuilt"].plot.hist(bins=30, title="Year of construction");

Boxplot:

(df[["OrigPrice", "ListPrice", "SalePrice"]].plot.box());

df["class_ln_price"] = pd.cut(np.log(df["OrigPrice"]), bins=4, precision=0)

(
    df.set_index("datetime")
    .groupby([pd.Grouper(freq="A"), "class_ln_price"])["class_ln_price"]
    .count()
    .unstack()
    .plot.area(ylabel="Count")
);

Scatter plot:

df.plot.scatter(x="SquareFeet", y="SalePrice", alpha=0.7, ylim=(0, None));

Hexbin:

df.plot.hexbin(
    y="ListPrice",
    x="SPLPPct",
    gridsize=10,
    cmap="inferno_r",
    C="CostPerSqFt",
    sharex=False,
);

3.3. The pandas-profiling package

The EDA we did using the built-in pandas functions was a bit limited and user-input heavy. The pandas-profiling library aims to automate the legwork of EDA for you. It generates ‘profile’ reports from a pandas DataFrame. For each column, many statistics are computed and then relayed in an interactive HTML report.

Let’s generate a report on our dataset using the minimal=True setting (the default settings produce a lot of computationally expensive extras):

from pandas_profiling import ProfileReport


profile = ProfileReport(
    df, minimal=True, title="Profiling Report: Grinnell House Sales"
)
profile.to_notebook_iframe()

This is a full on report about everything in our dataset! We can see, for instance, that we have 17 numerical variables, 0 boolean, and 4 categorical (which includes string), and 1 datetime. The overview also tells us the number of observations, that we have 1.2% of values missing across all columns, and we have zero duplicates.

The warnings page shows where pandas-profiling really shines. It flags potential issues with the data that should be taken into account in any subsequent analysis. For example, although not relevant here, the report will say if there are very unbalanced classes in a low cardinality categorical variable.

Let’s explore a couple of the warnings that have been raised. Address has high cardinality; but that’s okay, we expect most addresses to be different because the turn-over of houses is not that frequent. We also see that there are a lot of missing values for LotSize.

Now let’s dig down into a specific variable, SalePrice. You can view more about it using the ‘Toggle details’ button. We get four extra pages of detail about this variable, in addition to the headline statistics: a comprehensive page of descriptive statistics, a page with a histogram of the variable, a page with a table of common values, and a page of extreme values.

3.4. The dataprep package

The dataprep package offers very similar functionality to pandas-profiling; it produces an in-depth report on the input data. There isn’t much difference between them in general apart from it seems like dataprep has slightly better support for string column types (check out the details of Address below) and is a little bit richer on visualisations (it has interactive plots that you can change the axes of-look at the interactions page below).

Do note, however, that some column types can create an error-which is why we’ll pass in all but the last column of our dataframe from the previous set of examples. In this case, having a bin-category column seems to be an issue.

from dataprep.eda import create_report

report = create_report(df.iloc[:, :-1])

/Users/aet/opt/anaconda3/envs/codeforecon/lib/python3.8/site-packages/dask/dataframe/accessor.py:48: FutureWarning: The default value of regex will change from True to False in a future version.
  out = getattr(getattr(obj, accessor, obj), attr)(*args, **kwargs)
/Users/aet/opt/anaconda3/envs/codeforecon/lib/python3.8/site-packages/dask/dataframe/accessor.py:48: FutureWarning: The default value of regex will change from True to False in a future version.
  out = getattr(getattr(obj, accessor, obj), attr)(*args, **kwargs)

/Users/aet/opt/anaconda3/envs/codeforecon/lib/python3.8/site-packages/dask/dataframe/accessor.py:48: FutureWarning: The default value of regex will change from True to False in a future version.
  out = getattr(getattr(obj, accessor, obj), attr)(*args, **kwargs)

from IPython.core.display import display


display(report)

Overview

Dataset Statistics

Number of Variables	16
Number of Rows	929
Missing Cells	206
Missing Cells (%)	1.4%
Duplicate Rows	10
Duplicate Rows (%)	1.1%
Total Size in Memory	206.1 KB
Average Row Size in Memory	227.2 B

Variable Types

Numerical	13
Categorical	2
DateTime	1

Variables

Date

numerical

Show Details

Distinct Count	709
Unique (%)	76.3%
Missing	0
Missing (%)	0.0%
Infinite	0
Infinite (%)	0.0%
Memory Size	14.5 KB
Mean	18223.2153
Minimum	16527
Maximum	20186
Zeros	0
Zeros (%)	0.0%
Negatives	0
Negatives (%)	0.0%

Stats Plots

Quantile Statistics

Minimum	16527
5-th Percentile	16657.6
Q1	17169
Median	18046
Q3	19281
95-th Percentile	19990.4
Maximum	20186
Range	3659
IQR	2112

Descriptive Statistics

Mean	18223.2153
Standard Deviation	1139.171
Variance	1.2977e+06
Sum	1.6929e+07
Skewness	0.1729
Kurtosis	-1.3933
Coefficient of Variation	0.06251

Address

categorical

Show Details

Distinct Count	806
Unique (%)	86.8%
Missing	0
Missing (%)	0.0%
Memory Size	71.9 KB

Stats Plots

Length

Mean	14.2573
Standard Deviation	2.4748
Median	13
Minimum	8
Maximum	23

Sample

1st row	1510 First Ave #11...
2nd row	1020 Center St
3rd row	918 Chatterton St
4th row	1023 & 1025 Spring...
5th row	503 2nd Ave

Letter

Count	6591
Lowercase Letter	5013
Space Separator	2804
Uppercase Letter	1578
Dash Punctuation	13
Decimal Number	3795

Bedrooms

categorical

Show Details

Distinct Count	9
Unique (%)	1.0%
Missing	0
Missing (%)	0.0%
Memory Size	59.9 KB

Stats Plots

Length

Mean	1
Standard Deviation	0
Median	1
Minimum	1
Maximum	1

Sample

1st row	2
2nd row	3
3rd row	4
4th row	3
5th row	3

Letter

Count	0
Lowercase Letter	0
Space Separator	0
Uppercase Letter	0
Dash Punctuation	0
Decimal Number	929

Baths

numerical

Show Details

Distinct Count	16
Unique (%)	1.7%
Missing	0
Missing (%)	0.0%
Infinite	0
Infinite (%)	0.0%
Memory Size	14.5 KB
Mean	1.7793
Minimum	0
Maximum	6
Zeros	12
Zeros (%)	1.3%
Negatives	0
Negatives (%)	0.0%

Stats Plots

Quantile Statistics

Minimum	0
5-th Percentile	1
Q1	1
Median	1.75
Q3	2
95-th Percentile	3
Maximum	6
Range	6
IQR	1

Descriptive Statistics

Mean	1.7793
Standard Deviation	0.7432
Variance	0.5523
Sum	1653
Skewness	0.9974
Kurtosis	2.6003
Coefficient of Variation	0.4177

SquareFeet

numerical

Show Details

Distinct Count	526
Unique (%)	57.7%
Missing	18
Missing (%)	1.9%
Infinite	0
Infinite (%)	0.0%
Memory Size	14.2 KB
Mean	1583.2151
Minimum	640
Maximum	6815
Zeros	0
Zeros (%)	0.0%
Negatives	0
Negatives (%)	0.0%

Stats Plots

Quantile Statistics

Minimum	640
5-th Percentile	884
Q1	1150
Median	1440
Q3	1833
95-th Percentile	2855
Maximum	6815
Range	6175
IQR	683

Descriptive Statistics

Mean	1583.2151
Standard Deviation	655.665
Variance	429896.5471
Sum	1.4423e+06
Skewness	2.1205
Kurtosis	8.0646
Coefficient of Variation	0.4141

LotSize

numerical

Show Details

Distinct Count	398
Unique (%)	53.7%
Missing	188
Missing (%)	20.2%
Infinite	0
Infinite (%)	0.0%
Memory Size	11.6 KB
Mean	0.7235
Minimum	0.02893
Maximum	55
Zeros	0
Zeros (%)	0.0%
Negatives	0
Negatives (%)	0.0%

Stats Plots

Quantile Statistics

Minimum	0.02893
5-th Percentile	0.14
Q1	0.2339
Median	0.2841
Q3	0.3702
95-th Percentile	1.82
Maximum	55
Range	54.9711
IQR	0.1363

Descriptive Statistics

Mean	0.7235
Standard Deviation	2.7257
Variance	7.4294
Sum	536.0862
Skewness	13.0898
Kurtosis	224.9221
Coefficient of Variation	3.7676

YearBuilt

numerical

Show Details

Distinct Count	101
Unique (%)	10.9%
Missing	0
Missing (%)	0.0%
Infinite	0
Infinite (%)	0.0%
Memory Size	14.5 KB
Mean	1945.9031
Minimum	1870
Maximum	2013
Zeros	0
Zeros (%)	0.0%
Negatives	0
Negatives (%)	0.0%

Stats Plots

Quantile Statistics

Minimum	1870
5-th Percentile	1900
Q1	1900
Median	1956
Q3	1973
95-th Percentile	2001.6
Maximum	2013
Range	143
IQR	73

Descriptive Statistics

Mean	1945.9031
Standard Deviation	37.2099
Variance	1384.5768
Sum	1.8077e+06
Skewness	-0.1125
Kurtosis	-1.3947
Coefficient of Variation	0.01912

YearSold

numerical

Show Details

Distinct Count	11
Unique (%)	1.2%
Missing	0
Missing (%)	0.0%
Infinite	0
Infinite (%)	0.0%
Memory Size	14.5 KB
Mean	2009.366
Minimum	2005
Maximum	2015
Zeros	0
Zeros (%)	0.0%
Negatives	0
Negatives (%)	0.0%

Stats Plots

Quantile Statistics

Minimum	2005
5-th Percentile	2005
Q1	2007
Median	2009
Q3	2012
95-th Percentile	2014
Maximum	2015
Range	10
IQR	5

Descriptive Statistics

Mean	2009.366
Standard Deviation	3.1241
Variance	9.7603
Sum	1.8667e+06
Skewness	0.1688
Kurtosis	-1.3769
Coefficient of Variation	0.001555

MonthSold

numerical

Show Details

Distinct Count	12
Unique (%)	1.3%
Missing	0
Missing (%)	0.0%
Infinite	0
Infinite (%)	0.0%
Memory Size	14.5 KB
Mean	6.831
Minimum	1
Maximum	12
Zeros	0
Zeros (%)	0.0%
Negatives	0
Negatives (%)	0.0%

Stats Plots

Quantile Statistics

Minimum	1
5-th Percentile	2
Q1	5
Median	7
Q3	9
95-th Percentile	12
Maximum	12
Range	11
IQR	4

Descriptive Statistics

Mean	6.831
Standard Deviation	3.0358
Variance	9.216
Sum	6346
Skewness	-0.07994
Kurtosis	-0.9231
Coefficient of Variation	0.4444

DaySold

numerical

Show Details

Distinct Count	31
Unique (%)	3.3%
Missing	0
Missing (%)	0.0%
Infinite	0
Infinite (%)	0.0%
Memory Size	14.5 KB
Mean	16.226
Minimum	1
Maximum	31
Zeros	0
Zeros (%)	0.0%
Negatives	0
Negatives (%)	0.0%

Stats Plots

Quantile Statistics

Minimum	1
5-th Percentile	1
Q1	8
Median	16
Q3	25
95-th Percentile	30
Maximum	31
Range	30
IQR	17

Descriptive Statistics

Mean	16.226
Standard Deviation	9.3968
Variance	88.3001
Sum	15074
Skewness	-0.09188
Kurtosis	-1.2452
Coefficient of Variation	0.5791

CostPerSqFt

numerical

Show Details

Distinct Count	856
Unique (%)	92.1%
Missing	0
Missing (%)	0.0%
Infinite	0
Infinite (%)	0.0%
Memory Size	14.5 KB
Mean	83.2916
Minimum	0
Maximum	258.79
Zeros	18
Zeros (%)	1.9%
Negatives	0
Negatives (%)	0.0%

Stats Plots

Quantile Statistics

Minimum	0
5-th Percentile	21.934
Q1	60.82
Median	82.88
Q3	105.77
95-th Percentile	143.968
Maximum	258.79
Range	258.79
IQR	44.95

Descriptive Statistics

Mean	83.2916
Standard Deviation	35.7622
Variance	1278.9379
Sum	77377.86
Skewness	0.1412
Kurtosis	0.4294
Coefficient of Variation	0.4294

OrigPrice

numerical

Show Details

Distinct Count	385
Unique (%)	41.4%
Missing	0
Missing (%)	0.0%
Infinite	0
Infinite (%)	0.0%
Memory Size	14.5 KB
Mean	146047.084
Minimum	5990
Maximum	695000
Zeros	0
Zeros (%)	0.0%
Negatives	0
Negatives (%)	0.0%

Stats Plots

Quantile Statistics

Minimum	5990
5-th Percentile	49900
Q1	89900
Median	129900
Q3	179000
95-th Percentile	313000
Maximum	695000
Range	689010
IQR	89100

Descriptive Statistics

Mean	146047.084
Standard Deviation	85174.1094
Variance	7.2546e+09
Sum	1.3568e+08
Skewness	1.8714
Kurtosis	5.8245
Coefficient of Variation	0.5832

ListPrice

numerical

Show Details

Distinct Count	373
Unique (%)	40.2%
Missing	0
Missing (%)	0.0%
Infinite	0
Infinite (%)	0.0%
Memory Size	14.5 KB
Mean	141399.1389
Minimum	10500
Maximum	695000
Zeros	0
Zeros (%)	0.0%
Negatives	0
Negatives (%)	0.0%

Stats Plots

Quantile Statistics

Minimum	10500
5-th Percentile	46360
Q1	88000
Median	125000
Q3	172000
95-th Percentile	292800
Maximum	695000
Range	684500
IQR	84000

Descriptive Statistics

Mean	141399.1389
Standard Deviation	82228.2018
Variance	6.7615e+09
Sum	1.3136e+08
Skewness	1.8469
Kurtosis	5.8706
Coefficient of Variation	0.5815

SalePrice

numerical

Show Details

Distinct Count	405
Unique (%)	43.6%
Missing	0
Missing (%)	0.0%
Infinite	0
Infinite (%)	0.0%
Memory Size	14.5 KB
Mean	133203.7201
Minimum	7000
Maximum	606000
Zeros	0
Zeros (%)	0.0%
Negatives	0
Negatives (%)	0.0%

Stats Plots

Quantile Statistics

Minimum	7000
5-th Percentile	37000
Q1	83000
Median	119340
Q3	162500
95-th Percentile	281900
Maximum	606000
Range	599000
IQR	79500

Descriptive Statistics

Mean	133203.7201
Standard Deviation	77959.6247
Variance	6.0777e+09
Sum	1.2375e+08
Skewness	1.6968
Kurtosis	4.8016
Coefficient of Variation	0.5853

SPLPPct

numerical

Show Details

Distinct Count	593
Unique (%)	63.8%
Missing	0
Missing (%)	0.0%
Infinite	0
Infinite (%)	0.0%
Memory Size	14.5 KB
Mean	93.564
Minimum	46.5
Maximum	111.02
Zeros	0
Zeros (%)	0.0%
Negatives	0
Negatives (%)	0.0%

Stats Plots

Quantile Statistics

Minimum	46.5
5-th Percentile	82.13
Q1	91.32
Median	94.92
Q3	97.56
95-th Percentile	100
Maximum	111.02
Range	64.52
IQR	6.24

Descriptive Statistics

Mean	93.564
Standard Deviation	6.9538
Variance	48.3548
Sum	86920.95
Skewness	-2.5043
Kurtosis	10.8721
Coefficient of Variation	0.07432

datetime

datetime

Distinct Count	709
Unique (%)	76.3%
Missing	0
Missing (%)	0.0%
Memory Size	14.5 KB
Minimum	2005-04-02 00:00:00
Maximum	2015-04-09 00:00:00

Stats Plots

Interactions

Correlations

Missing Values

3.5. Review

That’s it for this very brief tour of exploratory data analysis with code. You should know now how to do simple analysis of pandas dataframes using techniques from other chapters to create summary statistics, using styling of tables of summary statistics, and using the quick visualisation options that are built-in to pandas. You should also know where to go for more in-depth, heavy duty exploratory data analysis reports, namely pandas-profiling and dataprep.

2. Working with Data 4. Reading and Writing Files